import ru.niir.goodfil.db.GoodfilFactory
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import ru.niir.goodfil.db.GoodType
import ru.niir.goodfil.db.Good

/*НИИР - G, Home - E, GW - C*/
baseDir = "C:\\Documents and Settings\\Admin\\Рабочий стол\\gw_goods";
prices = ["Воздушные.xls", "Масляные.xls", "Топливные.xls", "Салонные.xls", "Щетки.xls"]
addCodes = [:]
new File("${baseDir}\\no_code2.csv").eachLine { String line ->
  try
  {
    arr = line.split(";");
    code = arr[0];
    value = arr[1];
    if (code != "" && value != "")
      addCodes[value] = code;
  }
  catch (e) {}
}

gf = GoodfilFactory.instance;
wb = new HSSFWorkbook(new FileInputStream("${baseDir}\\goods_real2.xls"));
sheet = wb.getSheetAt(0);

columns = [:]
row0 = sheet.getRow(0);
for (i = 0; i < row0.lastCellNum; i++)
{
  cell = row0.getCell(i);
  columns[cell.stringCellValue] = i;
}


goodTypes = gf.getGoodTypes();
badGoodTypes = [];
mask = ["корпусные":"Щетки стелоочистителей корпусные (стандарт)",
        "корпусные (для грузовых автомобилей)":"Щетки стеклоочистителей корпусные (стандарт) для грузовых автомобилей, автобусов и т.д.",
        "AG":"Воздушные/салонные фильтры",
        "стеклоочистительные клипсы":"Cтеклоочистительные клипсы GW",
        "Свечи зажигания GW":"Свечи зажигания GW",
        "FG":"Топливные фильтры",
        "OG":"Масляные фильтры",
        "сменные лезвия":"Сменные лезвия",
        "безкорпусные":"Щетки стеклоочистителей безкорпусные",
        "Аксессуары GW":"Аксессуары GW",
        "Каталоги":"Каталоги GW",
        "Турбо Старт GW":"Турбо Старт GW"];
prefixes = ["AG", "RR", "RR&RS", "WВ", "AWВ", "SWВ", "ARR", "SWB", "WB", "AWB", "GB", "OG", "FG", "HR", "RV", "AVX", "HT", "HS", "VR", "RW"]
delims = ["GW", "GoodWill", "GWill"]

def long findGoodType(name)
{
  for (goodType in goodTypes)
    if (goodType.name == name)
      return goodType.id;
  return -1;
}

err_cnt = 0;
nocodes_goods = [];
goods = []

for (i = 1; i < sheet.lastRowNum; i++)
{
  row = sheet.getRow(i);
  goodCode1C        = row.getCell(columns['goodCode1C']).numericCellValue.toString();
  name              = row.getCell(columns['Naimenovanie']).stringCellValue;
  isGoodType        = row.getCell(columns['Gryppa']).numericCellValue;
  parentGoodType    = row.getCell(columns['RoditelGryppa']).stringCellValue;
  goodTypeName      = row.getCell(columns['Roditel']).stringCellValue;

  if (isGoodType) continue;

  //
  //  Проверяем корректность группы товаров
  //

  if (mask.containsKey(goodTypeName)) goodTypeName = mask[goodTypeName];
  else
  {
    println "- ${i} ${goodTypeName}";
    System.exit 0;
  }

  if (goodTypeName == "")
  {
    println "-- ${i} ${row}";
    System.exit 0;
  }

  found = false;
  for (GoodType goodType in goodTypes)
    if (goodType.name.equals(goodTypeName)) found = true;

  if (!found) badGoodTypes.add(goodTypeName);

  //  ---------------------------------------------------------------------

  //
  //  Нормализация кода и кода 1С -----------------------------------------
  //

  code = "";
  prefixFound = 0;
  for (prefix in prefixes)
  {
    if (name.contains(" ${prefix} "))
    {
      delimWasFound = 0;
      for (delim in delims)
      {
        if (name.contains(" ${delim} "))
        {
          code = name.substring(0, name.indexOf("${delim} "));
          code -= prefix;
          code = prefix + " " + code;
          code = code
                  .replaceAll("  ", " ")
                  .trim()
                  .toUpperCase()
                  .replaceAll("А", "A")
                  .replaceAll("В", "B")
                  .replaceAll("Е", "E")
                  .replaceAll("К", "K")
                  .replaceAll("М", "M")
                  .replaceAll("Н", "H")
                  .replaceAll("О", "O")
                  .replaceAll("Р", "P")
                  .replaceAll("С", "C")
                  .replaceAll("Т", "T")
                  .replaceAll("У", "Y")
                  .replaceAll("Х", "X");
          delimWasFound = 1;
        }
      }

      if (!delimWasFound)
      {
        code = name.substring(0, name.indexOf("${prefix} "));
        code -= prefix;
        code = prefix + " " + code;
        code = code
                .replaceAll("  ", " ")
                .trim()
                .toUpperCase()
                .replaceAll("А", "A")
                .replaceAll("В", "B")
                .replaceAll("Е", "E")
                .replaceAll("К", "K")
                .replaceAll("М", "M")
                .replaceAll("Н", "H")
                .replaceAll("О", "O")
                .replaceAll("Р", "P")
                .replaceAll("С", "C")
                .replaceAll("Т", "T")
                .replaceAll("У", "Y")
                .replaceAll("Х", "X");
      }
      prefixFound = 1;
    }
  }
  if (prefixFound == 0 && name.length() > 4 && name[4] == ' ')
  {
    code = name.substring(0, 4);
  }
  if (addCodes.containsKey(name))
  {
    code = addCodes[name];
  }

  //  Выводим товары без кода
  if (code == "")
  {
    code = "ERR_" + err_cnt;
    err_cnt++;
    println "no_code;${name}";
  }

  code = code.replaceAll("  ", " ");

  if (goodCode1C.length() == 1) goodCode1C = "0000000" + goodCode1C;
  if (goodCode1C.length() == 2) goodCode1C = "000000" + goodCode1C;
  if (goodCode1C.length() == 3) goodCode1C = "00000" + goodCode1C;
  if (goodCode1C.length() == 4) goodCode1C = "0000" + goodCode1C;
  if (goodCode1C.length() == 5) goodCode1C = "000" + goodCode1C;
  if (goodCode1C.length() == 6) goodCode1C = "00" + goodCode1C;
  if (goodCode1C.length() == 7) goodCode1C = "0" + goodCode1C;

  //  --------------------------------------------------------------------------------------

  Good good = new Good();
  good.code = code;
  good.name = name;
  good.c1code = goodCode1C;
  good.goodTypeId = findGoodType(goodTypeName);
  good.minPrice = 1;
  goods.add(good);
}


//  Выводим список отсутствующих групп
println "Bad goodTypes: ${(badGoodTypes as Set).size()}"
for (badGoodType in (badGoodTypes as Set))
  println "  * ${badGoodType}";

if (badGoodTypes.size() > 0) System.exit(0);

//  Выводим все коды товаров в файл c1_goods_codes.txt
File codesFile = new File("${baseDir}\\c1_goods_codes.txt");
codesFile << goods.collect { it.code }.join(",");

//  Формируем список кодов несоответствий между 1С и прайсом
badPricelistCodes = [];
for (String pricelist in prices)
{
  println "Processing ${pricelist}";
  wb = new HSSFWorkbook(new FileInputStream("${baseDir}\\${pricelist}"));
  sheet = wb.getSheetAt(0);

  columns = [:]
  row0 = sheet.getRow(0);
  for (i = 1; i < sheet.lastRowNum; i++)
  {
    code = sheet.getRow(i).getCell(0).stringCellValue;

    code = code.toUpperCase();
    code = code.replaceAll("  ", " ")
                .trim()
                .toUpperCase()
                .replaceAll("А", "A")
                .replaceAll("В", "B")
                .replaceAll("Е", "E")
                .replaceAll("К", "K")
                .replaceAll("М", "M")
                .replaceAll("Н", "H")
                .replaceAll("О", "O")
                .replaceAll("Р", "P")
                .replaceAll("С", "C")
                .replaceAll("Т", "T")
                .replaceAll("У", "Y")
                .replaceAll("Х", "X");
    boolean found = 0;
    for (Good good in goods)
      if (good.code == code) found = 1;
    if (!found)
    {
      badPricelistCodes << code;
    }
  }
}

//  Выводим отсутствующие
println "Bad pricelist codes: ${badPricelistCodes.size()}";
for (String badPricelistCode in badPricelistCodes)
  println "bad_pricelist_code;${badPricelistCode};код товара в 1С;новый код в прайсе;";

//println "Goods";
//for (good in goods)
//  println "${good.code};${good.name}"

println "finished";

/*
println "saving";
for (Good good in goods)
{
  try
  {
    gf.save(good);
  }
  catch (ValidationException ve)
  {
    good.c1code = "ERR_" + err_cnt;
    err_cnt++;
  }
}
println "saved";
*/
